package sk.stuba.fiit.foo07.genex.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import sk.stuba.fiit.foo07.genex.beans.TUser;

/**
 * @author palo
 */

public class UserDaoDerby extends BasicDao implements UserDao {

    private static final String sqlAddTUser = "INSERT INTO TUser VALUES(?, ?, ?, ?, ?, ?, DEFAULT)";
    private static final String sqldeleteTUserByID = "DELETE FROM TUser WHERE userID = ?";
    private static final String sqlGetTUserByID = "SELECT * FROM TUser WHERE userID = ?";
    private static final String sqlUpdateTUser = "UPDATE Question SET Name = ?, Surname = ?,Login = ?, Password= ?, Email= ?, Comment= ? WHERE userID = ?";
    private static final String sqlGetDefaultTUser = "SELECT * FROM TUser WHERE userID = 1";

    protected ResultSet rs;
    protected PreparedStatement st;
    protected TUser tu;

    public UserDaoDerby(Connection con) {
        super(con);
    }

    private void resultSetToTUser() throws SQLException {
        tu.setName(rs.getString("Name"));
        tu.setSurname(rs.getString("Surname"));
        tu.setLogin(rs.getString("Login"));
        tu.setPassword(rs.getString("Password"));
        tu.setEmail(rs.getString("Email"));
        tu.setComment(rs.getString("Comment"));
    }

    @Override
    public void addTUser(TUser toAdd) throws SQLException {
        try {
            st = con.prepareStatement(sqlAddTUser,
                    Statement.RETURN_GENERATED_KEYS);

            st.setString(1, toAdd.getName());
            st.setString(2, toAdd.getSurname());
            st.setString(3, toAdd.getLogin());
            st.setString(4, toAdd.getPassword());
            st.setString(5, toAdd.getEmail());
            st.setString(6, toAdd.getComment());

            st.execute();

            ResultSet rs = st.getGeneratedKeys();
            rs.next();

            toAdd.setUserID(rs.getInt(1));

        } catch (SQLException e) {
            st = null;
            throw e;
        }

    }

    @Override
    public void deleteTUser(TUser toDelete) throws SQLException {
        try {

            st = con.prepareStatement(sqldeleteTUserByID);
            st.setInt(1, toDelete.getUserID());

            st.executeUpdate();

        } catch (SQLException e) {
            st = null;
            throw e;
        }

    }

    @Override
    public TUser getDefaultTUser() throws SQLException {
        try {

            st = con.prepareStatement(sqlGetDefaultTUser);
            rs = st.executeQuery();

            rs.next();
            tu = new TUser();
            tu.setUserID(1); //default user has always userID = 1 ...
            resultSetToTUser();
            rs.close();

        } catch (SQLException e) {
            tu = null;
            st = null;
            rs = null;
            throw e;
        }

        return tu;
    }

    @Override
    public TUser getTUserByID(Integer userID) throws SQLException {
        try {

            st = con.prepareStatement(sqlGetTUserByID);

            st.setInt(1, userID);
            rs = st.executeQuery();

            rs.next();
            tu = new TUser();
            tu.setUserID(userID);
            resultSetToTUser();
            rs.close();

        } catch (SQLException e) {
            tu = null;
            st = null;
            rs = null;
            throw e;
        }

        return tu;
    }

    @Override
    public void updateTUser(TUser toUpdate) throws SQLException {
        try {
            st = con.prepareStatement(sqlUpdateTUser);

            st.setString(1, toUpdate.getName());
            st.setString(2, toUpdate.getSurname());
            st.setString(3, toUpdate.getLogin());
            st.setString(4, toUpdate.getPassword());
            st.setString(5, toUpdate.getEmail());
            st.setString(6, toUpdate.getComment());
            st.setInt(7, toUpdate.getUserID());

            st.executeUpdate();

        } catch (SQLException e) {
            st = null;
            throw e;
        }
    }

}
